﻿Imports System.IO
Imports System.Text
Imports Microsoft
Imports System.Data.SqlClient
Imports System.Data.Sql
Imports qLDataLayer
Imports Microsoft.ApplicationBlocks.Data
Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Nmo
Imports System.Reflection
Imports System.Collections.Specialized
Imports System.Data.OleDb
Imports System.Threading
Imports ICSharpCode.SharpZipLib.Checksums
Imports ICSharpCode.SharpZipLib.Zip
Imports ICSharpCode.SharpZipLib.GZip

Public Class frmRestoreData
    Private RestorePath As String = GetCurrentPath() + "RestoreTemp\" 'Biến lưu đường dẫn file backup CSDL
    Private oServer As SQLDMO.SQLServer2
    Private sConnectString As String = ""

#Region "Hàm/thủ tục"

    Private Function GetCurrentPath() As String
        Dim sPath As String = System.Reflection.Assembly.GetExecutingAssembly.Location
        Dim n As Integer = InStrRev(sPath, "\")
        If (n > 0) Then
            sPath = Mid(sPath, 1, n)
        End If
        Return sPath
    End Function

    Private Function GetStringConnect() As String
        sConnectString = "Data Source=" & txtServerName.Text & ";Initial Catalog=" & txtDatabaseName.Text & ";Persist Security Info=True;User ID=" & txtUserName.Text & ";Password=" & txtPassword.Text
        Return sConnectString
    End Function

    Private Function ConnectToDatabaseAndRefesh(ByVal sServerName As String, ByVal sDatabaseName As String, ByVal sUserName As String, ByVal sPassword As String) As Boolean
        oServer = New SQLDMO.SQLServer2
        oServer.EnableBcp = True
        oServer.Connect(sServerName, sUserName, sPassword)
        oDatabase = Nothing
        Try
            oDatabase = oServer.Databases.Item(sDatabaseName)
            oDatabase.DBOption.SelectIntoBulkCopy = True
            Return True
        Catch ex As Exception
            Return False
        End Try
    End Function

    Private Sub LoadSettingsFrom(ByVal sFileName As String)
        Dim oDataset As New DataSet
        If Not My.Computer.FileSystem.FileExists(sFileName) Then
            Exit Sub
        End If
        If (My.Computer.FileSystem.FileExists(sFileName)) Then
            oDataset.ReadXml(sFileName)
            Dim oDatatable As DataTable
            'Load Preset combo box
            oDatatable = oDataset.Tables("Preset")

            'Fill default values for server, database etc.
            oDatatable = oDataset.Tables(0)
            If oDatatable.Rows.Count > 0 Then
                txtServerName.Text = oDatatable.Rows(0)("Server")
                txtDatabaseName.Text = oDatatable.Rows(0)("Database")
                txtUserName.Text = oDatatable.Rows(0)("UserName")
                txtPassword.Text = oDatatable.Rows(0)("Password")
            Else
                txtServerName.Text = ""
                txtDatabaseName.Text = ""
                txtUserName.Text = ""
                txtPassword.Text = ""
            End If
        End If
    End Sub

    Private Sub LoadDataGrid()
        With grdTableList.DisplayLayout.Bands(0)
            .Columns("TableName").Header.Caption = "Tên bảng"
            .Columns("Select").Header.Caption = "Chọn"
            .Columns("ScriptSQL").Header.Caption = "Cấu trúc lệnh SQL"
            .Columns("Status").Header.Caption = "Trạng thái"
            .Columns("ScriptSQL").Hidden = True
            .Columns("TableName").Width = 200
            .Columns("Select").Width = 100
            .Columns("Status").Width = grdTableList.Width - .Columns("TableName").Width - .Columns("Select").Width
        End With

        With grdViewList.DisplayLayout.Bands(0)
            .Columns("ViewName").Header.Caption = "Tên view"
            .Columns("Select").Header.Caption = "Chọn"
            .Columns("ScriptSQL").Header.Caption = "Cấu trúc lệnh SQL"
            .Columns("Status").Header.Caption = "Trạng thái"
            .Columns("ScriptSQL").Hidden = True
            .Columns("ViewName").Width = 200
            .Columns("Select").Width = 100
            .Columns("Status").Width = grdTableList.Width - .Columns("ViewName").Width - .Columns("Select").Width
        End With

        With grdSPList.DisplayLayout.Bands(0)
            .Columns("SPName").Header.Caption = "Tên thủ tục"
            .Columns("Select").Header.Caption = "Chọn"
            .Columns("ScriptSQL").Header.Caption = "Cấu trúc lệnh SQL"
            .Columns("Status").Header.Caption = "Trạng thái"
            .Columns("ScriptSQL").Hidden = True
            .Columns("SPName").Width = 300
            .Columns("Select").Width = 100
            .Columns("Status").Width = grdTableList.Width - .Columns("SPName").Width - .Columns("Select").Width
        End With

        With grdUDFList.DisplayLayout.Bands(0)
            .Columns("UDFName").Header.Caption = "Tên hàm người dùng định nghĩa"
            .Columns("Select").Header.Caption = "Chọn"
            .Columns("ScriptSQL").Header.Caption = "Cấu trúc lệnh SQL"
            .Columns("Status").Header.Caption = "Trạng thái"
            .Columns("ScriptSQL").Hidden = True
            .Columns("UDFName").Width = 300
            .Columns("Select").Width = 100
            .Columns("Status").Width = grdTableList.Width - .Columns("UDFName").Width - .Columns("Select").Width
        End With

        With grdUDDList.DisplayLayout.Bands(0)
            .Columns("UDDName").Header.Caption = "Tên kiểu DL người dùng định nghĩa"
            .Columns("Select").Header.Caption = "Chọn"
            .Columns("ScriptSQL").Header.Caption = "Cấu trúc lệnh SQL"
            .Columns("Status").Header.Caption = "Trạng thái"
            .Columns("ScriptSQL").Hidden = True
            .Columns("UDDName").Width = 300
            .Columns("Select").Width = 100
            .Columns("Status").Width = grdTableList.Width - .Columns("UDDName").Width - .Columns("Select").Width
        End With

        With grdUserList.DisplayLayout.Bands(0)
            .Columns("UserName").Header.Caption = "Tên người dùng"
            .Columns("Select").Header.Caption = "Chọn"
            .Columns("ScriptSQL").Header.Caption = "Cấu trúc lệnh SQL"
            .Columns("Status").Header.Caption = "Trạng thái"
            .Columns("ScriptSQL").Hidden = True
            .Columns("UserName").Width = 300
            .Columns("Select").Width = 100
            .Columns("Status").Width = grdTableList.Width - .Columns("UserName").Width - .Columns("Select").Width
        End With

    End Sub

    Private Function AskOpenFile() As String
        Dim fileD As New OpenFileDialog
        fileD.Filter = "Zip Files | *.zip"
        If fileD.ShowDialog() = Windows.Forms.DialogResult.OK Then
            Return fileD.FileName
        Else
            Return ""
        End If
    End Function
    'Hàm đọc file nén
    Private Sub UnzipIT(ByVal sFileName As String, ByVal sDirName As String)
        Dim s As New ZipInputStream(File.OpenRead(sFileName))
        Dim theEntry As ZipEntry
        theEntry = s.GetNextEntry()
        Do While Not (IsNothing(theEntry))
            Dim directoryName As String = Path.GetDirectoryName(theEntry.Name)
            Dim fileName As String = Path.GetFileName(theEntry.Name)
            Directory.CreateDirectory(sDirName)
            Dim sWriter As FileStream
            sWriter = File.Create(sDirName + fileName)
            Dim size As Integer = 2048
            Dim data(2048) As Byte
            Do While (True)
                size = s.Read(data, 0, 2049)
                If (size > 0) Then
                    sWriter.Write(data, 0, size)
                Else
                    GoTo ex1
                End If
            Loop
EX1:
            sWriter.Close()
            theEntry = s.GetNextEntry
        Loop
        s.Close()
    End Sub

    Private Sub GetObjects_InTable(ByRef oTable As DataTable, ByRef oDatagird As Infragistics.Win.UltraWinGrid.UltraGrid, ByVal ObjectTypeName As String)
        Dim i As Integer
        Dim oDatatable As New DataTable
        oDatatable.Columns.Add(ObjectTypeName, "".GetType)
        oDatatable.Columns.Add("Select", True.GetType)
        oDatatable.Columns.Add("ScriptSQL", "".GetType)
        oDatatable.Columns.Add("Status", "".GetType)
        For i = 0 To oTable.DefaultView.Count - 1
            oDatatable.Rows.Add(New Object() {oTable.DefaultView(i)("ObjectName"), True, oTable.DefaultView(i)("ScriptSQL")})
        Next
        oDatagird.DataSource = oDatatable
    End Sub
    'Hàm đọc file backup CSDL
    Private Sub ReadExtractedDatabaseFile()
        Try
            ProgressBar1.Value = 3
            Dim oDataset As New DataSet
            oDataset.ReadXml(RestorePath + "CSDLSaoLuu.xml")
            If oDataset.Tables.Count > 0 Then
                Dim DT As DataTable = oDataset.Tables(0)
                DT.DefaultView.RowFilter = "ObjectType='table'"
                GetObjects_InTable(DT, grdTableList, "TableName")
                ProgressBar1.Value = 4
                DT.DefaultView.RowFilter = "ObjectType='view'"
                GetObjects_InTable(DT, grdViewList, "ViewName")
                ProgressBar1.Value = 5
                DT.DefaultView.RowFilter = "ObjectType='sp'"
                GetObjects_InTable(DT, grdSPList, "SPName")
                ProgressBar1.Value = 8
                DT.DefaultView.RowFilter = "ObjectType='udf'"
                GetObjects_InTable(DT, grdUDFList, "UDFName")
                ProgressBar1.Value = 7
                DT.DefaultView.RowFilter = "ObjectType='udd'"
                GetObjects_InTable(DT, grdUDDList, "UDDName")
                ProgressBar1.Value = 7
                DT.DefaultView.RowFilter = "ObjectType='user'"
                GetObjects_InTable(DT, grdUserList, "UserName")
                ProgressBar1.Value = 8
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString, "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

    'Hàm tạo CSDL
    Private Sub CreateNewDatabase(ByVal sDatabase As String)
        Dim oConn As New SqlConnection(GetStringConnect)
        Dim oSqlCmd As New SqlCommand("create database " + sDatabase, oConn)
        oConn.Open()
        oSqlCmd.ExecuteNonQuery()
        oConn.Close()
        oServer.Databases.Refresh()
        oDatabase = oServer.Databases.Item(sDatabase)
    End Sub

    Private Sub DropDatabase(ByVal sDatabase As String)
        Try
            oDatabase = Nothing
            oServer.DisConnect()
        Catch ex As Exception
            MessageBox.Show(ex.ToString, "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
        Dim oConn As New SqlConnection(GetStringConnect)
        Dim oSqlCmd As New SqlCommand("DROP DATABASE " + sDatabase, oConn)
        oConn.Open()
        oSqlCmd.ExecuteNonQuery()
        oConn.Close()
        Try
            oServer.Connect()
        Catch ex As Exception
            MessageBox.Show(ex.ToString, "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

    Private Function ExecuteScriptWithDependency(ByRef DTObjects As DataTable, ByRef lblObjectName As Infragistics.Win.Misc.UltraLabel, ByRef progressbar1 As ProgressBar) As Boolean
        Dim sScript As String = ""
        Dim sObjectName As String = ""
        progressbar1.Minimum = 0
        progressbar1.Maximum = DTObjects.Rows.Count
        For i As Integer = 0 To DTObjects.Rows.Count - 1
            sObjectName = DTObjects.Rows(i)("ObjectName")
            sScript = DTObjects.Rows(i)("ScriptSQL")
            If sScript <> "" Then
                Try
                    lblObjectName.Text = sObjectName
                    oDatabase.ExecuteImmediate(sScript)
                    DTObjects.Rows(i)("Executed") = True
                    DTObjects.Rows(i)("Status") = "Đã tạo."
                Catch ex As Exception
                    DTObjects.Rows(i)("Executed") = False
                    DTObjects.Rows(i)("Status") = ex.Message
                End Try
            End If
            progressbar1.Value = i
        Next
        Dim repeat As Integer
        For repeat = 0 To 10
            For i As Integer = 0 To DTObjects.Rows.Count - 1
                If DTObjects.Rows(i)("Executed") = False Then
                    sObjectName = DTObjects.Rows(i)("ObjectName")
                    sScript = DTObjects.Rows(i)("ScriptSQL")
                    If sScript <> "" Then
                        Try
                            oDatabase.ExecuteImmediate(sScript, SQLDMO.SQLDMO_EXEC_TYPE.SQLDMOExec_Default)
                            DTObjects.Rows(i)("Executed") = True
                            DTObjects.Rows(i)("Status") = "Đã tạo"
                        Catch ex As Exception
                            DTObjects.Rows(i)("Executed") = False
                            DTObjects.Rows(i)("Status") = ex.Message
                        End Try
                    End If
                End If
            Next
        Next
        Return True
    End Function

    Private Sub GetStatus(ByRef DTObjects As DataTable, ByRef dg As Infragistics.Win.UltraWinGrid.UltraGrid, ByRef ObjectNameColumn As String)
        Dim i As Integer
        If Not IsNothing(dg.DataSource) Then
            Dim dt As DataTable = dg.DataSource
            For i = 0 To dt.Rows.Count - 1
                DTObjects.DefaultView.RowFilter = "ObjectName='" + dt.Rows(i)(ObjectNameColumn) + "'"
                If DTObjects.DefaultView.Count > 0 Then
                    dt.Rows(i)("status") = DTObjects.DefaultView(0)("status")
                End If
            Next
            DTObjects.DefaultView.RowFilter = Nothing
        End If
    End Sub

    Private Function ImportsData(ByVal sTableName As String, ByVal sFilePath As String, ByVal bDeleteExistingData As Boolean) As String
        Try
            Dim objBCP As New SQLDMO.BulkCopy2
            objBCP.DataFilePath = sFilePath + sTableName + ".dat"
            If My.Computer.FileSystem.FileExists(objBCP.DataFilePath) Then
                objBCP.DataFileType = SQLDMO.SQLDMO_DATAFILE_TYPE.SQLDMODataFile_NativeFormat
                objBCP.UseExistingConnection = True
                objBCP.ServerBCPKeepIdentity = True
                objBCP.IncludeIdentityValues = True
                If bDeleteExistingData Then
                    oDatabase.ExecuteImmediate("delete from " + sTableName)
                End If
                oDatabase.Tables.Refresh()
                oDatabase.Tables.Item(sTableName).ImportData(objBCP)
                objBCP = Nothing
                Return "Dữ liệu đã được phục hồi"
            Else
            End If
        Catch ex As Exception
            Return (ex.Message)
        End Try
        Return ""
    End Function

    Private Sub RestoreData()
        'lbStatus1.Text = "Khôi phục dữ liệu"
        Dim i As Integer
        If Not IsNothing(grdTableList.DataSource) Then
            Dim DT As DataTable = CType(grdTableList.DataSource, DataTable)
            ProgressBar1.Value = 0
            ProgressBar1.Minimum = 0
            ProgressBar1.Maximum = DT.Rows.Count
            For i = 0 To DT.Rows.Count - 1
                If DT.Rows(i)("select") = True Then
                    Try
                        lbStatus2.Text = DT.Rows(i)("TableName").ToString()
                        DT.Rows(i)("status") += " " + Me.ImportsData(DT.Rows(i)("TableName"), RestorePath, chkDeleteExistingData.Checked)
                    Catch ex As Exception
                        MsgBox(ex.ToString)
                    End Try
                End If
                ProgressBar1.Value = i
            Next
        End If
    End Sub

    Private Sub ImportDataAndStructure()
        Try
            If chkStructure.Checked Then
                Dim DS As New DataSet
                'lbStatus2.Text = "Đọc file XML"
                DS.ReadXml(RestorePath + "CSDLSaoLuu.xml")
                If DS.Tables.Count > 0 Then
                    Dim DT As DataTable = DS.Tables(0)
                    Dim i As Integer
                    DT.Columns.Add("Executed", False.GetType)
                    DT.Columns.Add("Select", False.GetType)
                    DT.Columns.Add("Status")
                    For i = 0 To DT.Rows.Count - 1
                        DT.Rows(i)("Executed") = False
                        DT.Rows(i)("Select") = False
                        DT.Rows(i)("Status") = ""
                    Next
                    GetSelectedObjects(DT, grdTableList, "TableName")
                    GetSelectedObjects(DT, grdViewList, "ViewName")
                    GetSelectedObjects(DT, grdSPList, "SPName")
                    GetSelectedObjects(DT, grdUDFList, "UDFName")
                    GetSelectedObjects(DT, grdUDDList, "UDDName")
                    GetSelectedObjects(DT, grdUserList, "UserName")

                    Me.ExecuteScriptWithDependency(DT, lbStatus1, ProgressBar1)

                    GetStatus(DT, grdTableList, "TableName")
                    GetStatus(DT, grdViewList, "ViewName")
                    GetStatus(DT, grdSPList, "SPName")
                    GetStatus(DT, grdUDFList, "UDFName")
                    GetStatus(DT, grdUDDList, "UDDName")
                    GetStatus(DT, grdUserList, "UserName")

                End If
            End If
            If chkData.Checked Then
                Me.RestoreData()
            End If
            Try
                My.Computer.FileSystem.DeleteDirectory(RestorePath, FileIO.DeleteDirectoryOption.DeleteAllContents)
            Catch ex As Exception
                MessageBox.Show(ex.Message(), "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
            MessageBox.Show("Đã phục hồi dữ liệu thành công", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information)
        Catch ex As Exception
            MessageBox.Show(ex.Message(), "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

    Private Sub GetSelectedObjects(ByRef DTObjects As DataTable, ByRef dg As Infragistics.Win.UltraWinGrid.UltraGrid, ByRef ObjectNameColumn As String)
        Dim i As Integer
        If Not IsNothing(dg.DataSource) Then
            Dim dt As DataTable = dg.DataSource
            For i = 0 To DTObjects.Rows.Count - 1
                dt.DefaultView.RowFilter = ObjectNameColumn + "='" + DTObjects.Rows(i)("ObjectName") + "'"
                If dt.DefaultView.Count > 0 Then
                    DTObjects.Rows(i)("Select") = dt.DefaultView(0)("Select")
                End If
            Next
            dt.DefaultView.RowFilter = Nothing
        End If
    End Sub

    'Hàm khôi phục dữ liệu
    Private Sub RestoreDatabase()
        If (txtFileBackUp.Text = "") Then
            MessageBox.Show("Bạn chưa chọn tệp tin CSDL sao lưu", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Exit Sub
        End If
        Try
            lbStatus2.Text = "Đang kiểm tra CSDL"
            Me.ConnectToDatabaseAndRefesh(txtServerName.Text, txtDatabaseName.Text, txtUserName.Text, txtPassword.Text)
            If (IsNothing(oDatabase)) Then
                lbStatus2.Text = "Tạo CSDL"
                Me.CreateNewDatabase(txtDatabaseName.Text)
            Else
                If (Not rdDrop.Checked And Not rdAppend.Checked) Then
                    MessageBox.Show("CSDL đã tồn tại đã hay chọn chức năng ghi đè CSDL", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error)
                    lbStatus2.Text = ""
                    Exit Sub
                End If
                If (rdDrop.Checked) Then
                    lbStatus2.Text = "Xóa CSDL"
                    Me.DropDatabase(txtDatabaseName.Text)
                    lbStatus2.Text = "Tạo CSDL"
                    Me.CreateNewDatabase(txtDatabaseName.Text)
                End If
            End If
            If chkData.Checked = False And chkStructure.Checked = False Then
                MessageBox.Show("Bạn chưa chọn kiểu phục hồi dữ liệu", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error)
                Exit Sub
            End If
            ProgressBar1.Visible = True
            ProgressBar1.Maximum = 8
            ProgressBar1.Value = 1
            Me.ImportDataAndStructure()
        Catch ex As Exception
            MessageBox.Show(ex.ToString, "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

#End Region

    Private Sub frmRestoreDatabase_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Try
            lbStatus1.Visible = False
            lbStatus2.Visible = False
            ProgressBar1.Visible = False
            LoadSettingsFrom(My.Application.Info.DirectoryPath + "\DefaultValues.backupsettings")
        Catch ex As Exception
            MessageBox.Show(ex.ToString, "Thông báo lỗi", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

    Private Sub btnOpenfile_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOpenfile.Click
        Dim sFile As String = Me.AskOpenFile()
        If sFile <> "" Then
            txtFileBackUp.Text = sFile
            Me.UnzipIT(txtFileBackUp.Text, RestorePath)
            Try
                ProgressBar1.Visible = True
                ProgressBar1.Maximum = 8
                ProgressBar1.Value = 1
                Me.Refresh()
                ReadExtractedDatabaseFile()
                Me.LoadDataGrid()
                cmdRestoreData.Enabled = True
            Catch ex As Exception
                MessageBox.Show(ex.ToString, "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Finally
                ProgressBar1.Visible = False
            End Try
        End If
    End Sub

    Private Sub cmdRestoreData_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdRestoreData.Click
        Try
            lbStatus1.Visible = True
            lbStatus1.Text = "Đang trong quá trình phục hồi CSDL"
            Me.RestoreDatabase()
        Catch ex As Exception
            MessageBox.Show(ex.ToString, "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try

    End Sub

End Class